package com.crcc.common.util;

import com.crcc.common.constant.CommonConsts;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.util.*;

public class ImportExcelUtil {
    private final static String excel2003L = ".xls"; // 2003- 版本的excel
    private final static String excel2007U = ".xlsx"; // 2007+ 版本的excel
    private final static String PHONE_PATTERN = "^1(3[0-9]|4[0-9]|5[0-9]|6[0-9]|7[0-9]|8[0-9]|9[0-9])\\d{8}$";
    private final static String IDCARD_PATTERN = "(^[1-9]\\d{5}(18|19|([23]\\d))\\d{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)\\d{3}[0-9Xx]$)|(^[1-9]\\d{5}\\d{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)\\d{3}$)";
    /**
     * @Description: 获取IO流中的数据，组装成List<List<Object>>对象
     * @author: w_kongdk
     * @param: @param in
     * @param: @param fileName
     * @param: @param readFlag
     * @param: @return
     * @param: @throws Exception
     * @return: List<List < Object>>
     * @throws:
     */
    public static List<List<Object>> getBankListByExcel(InputStream in, String fileName, int readFlag)
            throws Exception {
        List<List<Object>> list = null;

        // 创建Excel工作薄
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        list = new ArrayList<List<Object>>();
        // 遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }
            // 遍历当前sheet中的所有行
            for (int j = 0; j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if (row == null || readFlag > j) {
                    continue;
                }
                boolean f = isRowEmpty(row);
                if (f) {
                    continue;
                }
                // 遍历所有的列
                List<Object> li = new ArrayList<Object>();
                for (int y = 0; y < sheet.getRow(0).getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    li.add(getCellValue(cell));
                }
                list.add(li);
            }
        }
        in.close();
        return list;
    }
    /**
     * @Description: 获取IO流中的数据，组装成List<List<Object>>对象
     * @author: w_kongdk
     * @param: @param in
     * @param: @param fileName
     * @param: @param readFlag
     * @param: @return
     * @param: @throws Exception
     * @return: List<List < Object>>
     * @throws:
     */
    public static List<List<Object>> getBankListByExcelEnd(InputStream in, String fileName, int readFlag,int length)
            throws Exception {
        List<List<Object>> list = null;

        // 创建Excel工作薄
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        list = new ArrayList<List<Object>>();
        // 遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }
            // 遍历当前sheet中的所有行
            for (int j = 0; j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if (row == null || readFlag > j) {
                    continue;
                }
                if (sheet.getLastRowNum() == j) {
                    continue;
                }
                boolean f = isRowEmpty(row);
                if (f) {
                    continue;
                }
                //如果是合计行，不计入
                cell = row.getCell(0);
                if("合计".equals(cell.getStringCellValue())){
                    continue;
                }
                //判断添加样式过滤掉最后一行
                if(cell.getStringCellValue().contains("办公室")){
                    continue;
                }
                // 遍历所有的列
                List<Object> li = new ArrayList<Object>();
                for (int y = 0; y < length; y++) {
                    cell = row.getCell(y);
                    li.add(getCellValue(cell));
                }
                list.add(li);
            }
        }
        in.close();
        return list;
    }

    public static List<List<Object>> getBankListByExcelEndNew(InputStream in, String fileName, int readFlag,int length)
            throws Exception {
        List<List<Object>> list = null;

        // 创建Excel工作薄
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        list = new ArrayList<List<Object>>();
        // 遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }
            // 遍历当前sheet中的所有行
            for (int j = 0; j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if (row == null || readFlag > j) {
                    continue;
                }
                if (sheet.getLastRowNum() == j) {
                    continue;
                }
                boolean f = isRowEmpty(row);
                if (f) {
                    continue;
                }
                cell = row.getCell(0);
                cell.setCellType(CellType.STRING);
                //判断添加样式过滤掉最后一行
                if(cell.getStringCellValue().contains("说明：\n" +
                        "1.如果务工人员为外籍人员无需填写民族。")){
                    continue;
                }
                // 遍历所有的列
                List<Object> li = new ArrayList<Object>();
                for (int y = 0; y < length; y++) {
                    cell = row.getCell(y);
                    li.add(getCellValue(cell));
                }
                list.add(li);
            }
        }
        in.close();
        return list;
    }

    /**
     * @Description: 获取IO流中的数据，组装成List<List<Object>>对象
     * @author: w_kongdk
     * @param: @param in
     * @param: @param fileName
     * @param: @param readFlag
     * @param: @return
     * @param: @throws Exception
     * @return: List<List < Object>>
     * @throws:
     */
    public static List<List<Object>> getBankListByExcel(InputStream in, String fileName, int readFlag,
                                                        int sheeetNum) throws Exception {
        List<List<Object>> list = null;
        // 创建Excel工作薄
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;
        list = new ArrayList<List<Object>>();
        // 遍历Excel中所有的sheet
        sheet = work.getSheetAt(sheeetNum);
        if (sheet == null) {
            return list;
        }
        // 遍历当前sheet中的所有行
        for (int j = 0; j < sheet.getLastRowNum(); j++) {
            row = sheet.getRow(j);
            if (row == null || readFlag > j) {
                continue;
            }
            boolean f = isRowEmpty(row);
            if (f) {
                continue;
            }
            // 遍历所有的列
            List<Object> li = new ArrayList<Object>();
            for (int y = 0; y < sheet.getRow(0).getLastCellNum(); y++) {
                cell = row.getCell(y);
                li.add(getCellValue(cell));
            }
            list.add(li);
        }
        in.close();
        return list;
    }


    public static boolean isRowEmpty(Row row) {
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != CellType.BLANK) {
                return false;
            }
        }
        return true;
    }

    /**
     * 描述：根据文件后缀，自适应上传文件的版本
     *
     * @param inStr,fileName
     * @return
     * @throws Exception
     */
    public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (excel2003L.equals(fileType)) {
            ZipSecureFile.setMinInflateRatio(-1.0d);
            wb = new HSSFWorkbook(inStr); // 2003-
        } else if (excel2007U.equals(fileType)) {
            ZipSecureFile.setMinInflateRatio(-1.0d);
            wb = new XSSFWorkbook(inStr); // 2007+
        } else {
            throw new Exception("解析的文件格式有误！");
        }
        return wb;
    }

    /**
     * 描述：对表格中数值进行格式化
     *
     * @param cell
     * @return
     */
    public static Object getCellValue(Cell cell) {
        Object value = "";
        DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符
//		是否四舍五入
        df.setRoundingMode(RoundingMode.HALF_UP);
//        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化  
//        DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字  
        if (cell != null) {
            switch (cell.getCellType()) {
                case FORMULA:
                    try {
                        // 有的excel有特殊列是公式,计算结果为数值,需要这么读具体结果
                        value = cell.getNumericCellValue();
                    } catch (Exception e) {
                        // 有的excel有特殊列是公式,计算结果为字符串,需要这么读具体结果
                        System.out.println("该列公式计算结果不是数值,需要按字符串读取");
                        value = cell.getStringCellValue();
                    }
                    break;
                case STRING:
                    value = cell.getRichStringCellValue().getString();
                    if (null != value) {
                        String string = value.toString();
                        if (string.indexOf(",") != -1) {
                            value = string.replace(",", "，");
                        }
                    }
                    break;
                case NUMERIC:
                    String d = cell.getNumericCellValue() + "";
                    if (d.length() > 2 && d.endsWith(".0")) {
                        value = df.format(cell.getNumericCellValue());
                    } else if (d.indexOf("E") > 0 && d.indexOf(".") > 0) {
                        BigDecimal db = new BigDecimal(d);
                        value = db.toPlainString();
                    } else {
                        value = cell.getNumericCellValue();
                    }
                    break;
                case BOOLEAN:
                    value = cell.getBooleanCellValue();
                    break;
                case BLANK:
                    value = "";
                    break;
                default:
                    break;
            }
        }
        return value;
    }

    /**
     * 创建下拉列表选项(单元格下拉框数据小于255字节时使用)
     *
     * @param sheet    所在Sheet页面
     * @param values   下拉框的选项值
     * @param firstRow 起始行（从0开始）
     * @param lastRow  终止行（从0开始）
     * @param firstCol 起始列（从0开始）
     * @param lastCol  终止列（从0开始）
     */
    public void createDropDownList(Sheet sheet, String[] values, int firstRow, int lastRow, int firstCol, int lastCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        DataValidationConstraint constraint = helper.createExplicitListConstraint(values);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        if (dataValidation instanceof HSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(false);
        } else {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        }
        sheet.addValidationData(dataValidation);
    }

    /**
     * 隐藏Sheet方式创建下拉框(单元格下拉框数据大于255字节时使用)
     *
     * @param sheet            需要添加下拉框的Sheet
     * @param firstRow         起始行
     * @param firstCol         起始列
     * @param endRow           终止行
     * @param endCol           终止列
     * @param dataArray        下拉框数组
     * @param wbCreat          所在excel的WorkBook，用于创建隐藏Sheet
     * @param hidddenSheetName 隐藏Sheet的名称
     * @return
     */
    public void createDropDownListWithHiddenSheet(Sheet sheet, int firstRow, int firstCol, int endRow, int endCol, String[] dataArray, Workbook wbCreat, String hidddenSheetName) {

        Sheet hidden = wbCreat.createSheet(hidddenSheetName);
        Cell cell = null;
        for (int i = 0, length = dataArray.length; i < length; i++) {
            String name = dataArray[i];
            Row row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(name);
        }
        Name namedCell = wbCreat.createName();
        namedCell.setNameName(hidddenSheetName);
        namedCell.setRefersToFormula(hidddenSheetName + "!$A$1:$A$" + dataArray.length);
        //sheet设置为隐藏
        wbCreat.setSheetHidden(wbCreat.getSheetIndex(hidden), true);
        //加载数据,将名称为hidden的
        DataValidationConstraint constraint = null;
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol,
                endCol);
        // 创建 DataValidation
        DataValidation validation = null;
        if (sheet instanceof XSSFSheet || sheet instanceof SXSSFSheet) {
            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            constraint = dvHelper.createFormulaListConstraint(hidddenSheetName);
            validation = dvHelper.createValidation(constraint, addressList);
        } else {
            constraint = DVConstraint.createFormulaListConstraint(hidddenSheetName);
            validation = new HSSFDataValidation(addressList, constraint);
        }
        if (validation instanceof HSSFDataValidation) {
            validation.setSuppressDropDownArrow(false);
        } else {
            validation.setSuppressDropDownArrow(true);
            validation.setShowErrorBox(true);
        }
        sheet.addValidationData(validation);
    }
    /**
     * 描述：手机号校验
     *
     * @param phoneNum
     * @return
     */
    public static boolean verifyPhoneNum(String phoneNum) {
        return phoneNum.matches(PHONE_PATTERN);
    }
/**
     * 描述：身份证号校验
     *
     * @param phoneNum
     * @return
     */
    public static boolean verifyIDCardNum(String phoneNum) {
        return phoneNum.matches(IDCARD_PATTERN);
    }

    public static List<List<Object>> getPaySalaryListByExcelEnd(InputStream in, String fileName, int readFlag,int length)
            throws Exception {
        List<List<Object>> list = null;

        // 创建Excel工作薄
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        list = new ArrayList<List<Object>>();
        // 遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }
            // 遍历当前sheet中的所有行
            for (int j = 0; j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if (row == null || readFlag > j) {
                    continue;
                }
                boolean f = isRowEmpty(row);
                if (f) {
                    continue;
                }
                // 遍历所有的列
                List<Object> li = new ArrayList<Object>();
                for (int y = 0; y < length; y++) {
                    cell = row.getCell(y);
                    if(y == 8){
                        if(cell.getCellType() != null){
                            if(cell.getCellType().name() == "STRING"){
                                if (cell.getStringCellValue() == null){
                                    li.add(getCellValue(cell));
                                }else {
                                    li.add(cell.getStringCellValue());
                                }
                            }else {
                                if (cell.getDateCellValue() == null){
                                    li.add(getCellValue(cell));
                                }else {
                                    li.add(cell.getDateCellValue());
                                }
                            }
                        }
                    }else{
                        li.add(getCellValue(cell));
                    }

                }
                list.add(li);
            }
        }
        in.close();
        return list;
    }

    public static List<List<Object>> getLaborRecruitByExcel(InputStream in, String fileName, int readFlag,int length)
            throws Exception {
        List<List<Object>> list = null;

        // 创建Excel工作薄
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        list = new ArrayList<List<Object>>();
        // 遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }
            // 遍历当前sheet中的所有行
            for (int j = 0; j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                cell = row.getCell(0);
                String value =  getCellValue(cell).toString();
                if(value.contains("备注：")){
                    break;
                }
                if (row == null || readFlag > j) {
                    continue;
                }
                if (sheet.getLastRowNum() == j) {
                    continue;
                }
                boolean f = isRowEmpty(row);
                if (f) {
                    continue;
                }
                // 遍历所有的列
                List<Object> li = new ArrayList<Object>();
                for (int y = 0; y < length; y++) {
                    cell = row.getCell(y);
                    li.add(getCellValue(cell));
                }
                list.add(li);
            }
        }
        in.close();
        return list;
    }

    /**
     * @Description: 获取IO流中的数据，组装成List<List<Object>>对象
     * @author: w_kongdk
     * @param: @param in
     * @param: @param fileName
     * @param: @param readFlag
     * @param: @return
     * @param: @throws Exception
     * @return: List<List < Object>>
     * @throws:
     */
    public static List<List<Object>> getFangyiListByExcel(InputStream in, String fileName, int readFlag)
            throws Exception {
        List<List<Object>> list = null;

        // 创建Excel工作薄
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        list = new ArrayList<List<Object>>();
        // 遍历Excel中所有的sheet
        sheet = work.getSheetAt(0);
        // 遍历当前sheet中的所有行
        for (int j = 0; j <= sheet.getLastRowNum(); j++) {
            row = sheet.getRow(j);
            if (row == null || readFlag > j) {
                continue;
            }
            boolean f = isRowEmpty(row);
            if (f) {
                continue;
            }
            // 遍历所有的列
            List<Object> li = new ArrayList<Object>();
            for (int y = 0; y < sheet.getRow(0).getLastCellNum(); y++) {
                cell = row.getCell(y);
                li.add(getCellValue(cell));
            }
            list.add(li);
        }
        in.close();
        return list;
    }

    /**
     * @Description: 批量上传证件照片
     * @author: w_kongdk
     * @param: @param in
     * @param: @param fileName
     * @param: @param readFlag
     * @param: @return
     * @param: @throws Exception
     * @return: List<List < Object>>
     * @throws:
     */
    public static List<List<Object>> getCredentialsListByExcel(InputStream in, String fileName, int readFlag)
            throws Exception {
        List<List<Object>> list = null;

        // 创建Excel工作薄
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        list = new ArrayList<List<Object>>();
        // 遍历Excel中所有的sheet
        sheet = work.getSheetAt(1);
        // 遍历当前sheet中的所有行
        for (int j = 0; j <= sheet.getLastRowNum(); j++) {
            row = sheet.getRow(j);
            if (row == null || readFlag > j) {
                continue;
            }
            boolean f = isRowEmpty(row);
            if (f) {
                continue;
            }
            // 遍历所有的列
            List<Object> li = new ArrayList<Object>();
            for (int y = 0; y < sheet.getRow(0).getLastCellNum(); y++) {
                cell = row.getCell(y);
                li.add(getCellValue(cell));
            }
            list.add(li);
        }
        in.close();
        return list;
    }


    public static void main(String[] args) throws Exception {
        File file = new File("/Users/mjx/Desktop/工资单明细.xls");
        List<Map<String, String>> maps = recordDetailOverseasExcel(new FileInputStream(file), file.getName());

        System.out.println(maps);
    }


    public static List<Map<String, String>> recordDetailOverseasExcel(InputStream in, String fileName){
        if ("xlsx".equals(getFileExtendsName(fileName))) {
            return readXlsxExcel(in);
        }
        if ("xls".equals(getFileExtendsName(fileName))) {
            return readXlsExcel(in);
        }
        return null;
    }


    /**
     * 读取excel的拓展名
     *
     * @param fileName
     * @return
     */
    public static String getFileExtendsName(String fileName) {
        if (fileName == null) {
            return null;
        }
        return fileName.substring(fileName.lastIndexOf(".") + 1);
    }


    /**
     * 读取03-07年的Excel的方法
     * @Author xmj
     * @Description 读取03-07年的Excel的方法
     * @Date 15:08 2023/3/10
     * @param in 文件流
     * @Return java.util.List<java.util.Map < java.lang.String, java.lang.String>>
     */
    private static List<Map<String, String>> readXlsExcel(InputStream in) {
        try {
            // 获取一个工作簿
            Workbook workbook = new HSSFWorkbook(in);

            // 获取一个工作表两种方式
            // HSSFSheet sheet = workbook.getSheet("sheet0");
            // 获取工作表的第二种方式
            HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(0);

            int firstRow = 0;
            List<String> titleList = new ArrayList<>();
            List<Map<String, String>> valueList = new ArrayList<>();
            // 获取sheet的最后一行
            int lastRow = sheet.getLastRowNum();
            for (int i = firstRow; i <= lastRow -1; i++) {
                HSSFRow row = sheet.getRow(i);
                if (row == null){
                    continue;
                }
                int lastCol = row.getLastCellNum();
                Map<String, String> paramsMap = new LinkedHashMap<>();
                for (int j = 0; j < lastCol; j++) {
                    HSSFCell cell = row.getCell(j);
                    if (cell == null) {
                        continue;
                    }
                    cell.setCellType(CellType.STRING);// 强制作为String处理
                    String value = cell.getStringCellValue();
                    if (i<= CommonConsts.THREE && StringUtils.isNotBlank(value)){
                        titleList.add(value);
                        paramsMap.put("title",value);
                        continue;
                    }
                    if (i > CommonConsts.THREE && CommonConsts.THREE + j < titleList.size()) {
                        paramsMap.put(titleList.get(CommonConsts.THREE + j), value);
                    }
                    System.out.print(value + "  ");
                }
                System.out.println();
                valueList.add(paramsMap);
            }
            return valueList;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * 读取07年以后的Excel的方法
     * @Author xmj
     * @Description 读取07年以后的Excel的方法
     * @Date 15:09 2023/3/10
     * @param in 文件流
     * @Return java.util.List<java.util.Map < java.lang.String, java.lang.String>>
     */
    private static List<Map<String, String>> readXlsxExcel(InputStream in) {
        try {
            // 第二种创建XSSFWorkbook的方法
            Workbook workbook = new XSSFWorkbook(in);

            // 获取一个工作表两种方式
            // HSSFSheet sheet = workbook.getSheet("sheet0");
            // 获取工作表的第二种方式
            XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(0);

            int firstRow = 0;
            // 获取sheet的最后一行
            int lastRow = sheet.getLastRowNum();
            List<String> titleList = new ArrayList<>();
            List<Map<String, String>> valueList = new ArrayList<>();
            for (int i = firstRow; i <= lastRow; i++) {
                XSSFRow row = sheet.getRow(i);
                int lastCol = row.getLastCellNum();
                Map<String, String> paramsMap = new LinkedHashMap<>();
                for (int j = 0; j < lastCol; j++) {
                    XSSFCell cell = row.getCell(j);
                    cell.setCellType(CellType.STRING);// 强制将读取的数据作为String处理，否则读取数字会报错

                    String value = cell.getStringCellValue();
                    if (i<= CommonConsts.THREE && StringUtils.isNotBlank(value)){
                        titleList.add(value);
                        paramsMap.put("title",value);
                        continue;
                    }
                    if (i > CommonConsts.THREE && CommonConsts.THREE + j < titleList.size()) {
                        paramsMap.put(titleList.get(CommonConsts.THREE + j), value);
                    }
                    System.out.print(value + "  ");
                }
                System.out.println();
                valueList.add(paramsMap);
            }
            return valueList;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * @Description: 获取IO流中的数据，组装成List<List<Object>>对象
     * @author: w_kongdk
     * @param: @param in
     * @param: @param fileName
     * @param: @param readFlag
     * @param: @return
     * @param: @throws Exception
     * @return: List<List < Object>>
     * @throws:
     */
    public static List<List<Object>> getUserLoginListByExcel(InputStream in, String fileName, int readFlag)
            throws Exception {
        List<List<Object>> list = null;

        // 创建Excel工作薄
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        list = new ArrayList<List<Object>>();
        // 遍历Excel中所有的sheet
        sheet = work.getSheetAt(0);
        // 遍历当前sheet中的所有行
        for (int j = 1; j <= sheet.getLastRowNum(); j++) {
            row = sheet.getRow(j);
            if (row == null || readFlag > j) {
                continue;
            }
            boolean f = isRowEmpty(row);
            if (f) {
                continue;
            }
            // 遍历所有的列
            List<Object> li = new ArrayList<Object>();
            for (int y = 0; y < sheet.getRow(1).getLastCellNum(); y++) {
                cell = row.getCell(y);
                li.add(getCellValue(cell));
            }
            list.add(li);
        }
        in.close();
        return list;
    }
}
